# encoding:utf-8
import pymysql
from DBUtils.PooledDB import PooledDB, SharedDBConnection
import jieba
from contextlib import contextmanager
from tqdm import tqdm
from pymysql.cursors import DictCursor

MYSQL_NAME='image'
MYSQL_USER = 'fzj'
MYSQL_HOST = "mysql.fzj.com"
MYSQL_PW = "FanYan879688"
MYSQL_PORT = 3306
pool = PooledDB(creator=pymysql,maxconnections=6,mincached=2,maxcached=5,maxshared=3,blocking=True,maxusage=None,setsession=[],ping=1,host=MYSQL_HOST,port=MYSQL_PORT,user=MYSQL_USER,password=MYSQL_PW,
            database=MYSQL_NAME,
            charset='utf8mb4'
        )

@contextmanager
def get_cursor():
    conn = pool.connection()
    # 默认就自动提交
    conn.autocommit = True
    try:
        cur=conn.cursor(DictCursor)
        yield cur
        # 或设置手动提交 con.commit()
    finally:
        # 用完放回连接池
        conn.commit()
        cur.close()
        conn.close()

# 数据存储
with get_cursor() as cur:
    # 数据分词
    cur.execute('select * from img_table group by dirname')
    res=cur.fetchall()
    for item in res:
        dirname=item['dirname']
        fenci_result=list(jieba.cut(dirname))
        fenci='/'.join(fenci_result)
        print(fenci)
        cur.execute('update img_table set fenci=%s where dirname=%s',(fenci,dirname))
        # 基于分词构建节点
        for node in fenci_result:
            cur.execute('select * from img_node where node_name=%s', (node,))
            res = cur.fetchone()
            if not res:
                cur.execute('insert into img_node value (NULL,%s)', (node,))
        # 分词的结果构建节点关系
        for index in range(1,len(fenci_result)):
            cur.execute('select l.* from img_link as l inner join img_node as s on l.source_id=s.id inner join img_node as t on l.target_id=t.id where s.node_name=%s and t.node_name=%s', (fenci_result[0],fenci_result[index]))
            res=cur.fetchone()
            if res:
                cur.execute('update img_link set weight=%s where source_id=%s and target_id=%s',(int(res['weight'])+1,res['source_id'],res['target_id']))
            else:
                cur.execute('insert into img_link (source_id,target_id) select (select id from img_node where node_name=%s),(select id from img_node where node_name=%s)',
                            (fenci_result[0],fenci_result[index]))